﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Chats
{
    internal static class ChatDAL
    {
        internal static ChatEntity GetChatByReader(IDataReader dr)
        {
            ChatEntity chat = new ChatEntity();

            chat.ChatID = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
            chat.Number = dr.IsDBNull(1) ? null : dr.GetString(1);
            chat.Name = dr.IsDBNull(2) ? null : dr.GetString(2);
            chat.ProvinceID = dr.IsDBNull(3) ? 0 : dr.GetInt32(3);
            chat.CityID = dr.IsDBNull(4) ? 0 : dr.GetInt32(4);
            chat.Created = Util.ConverDateTime(dr.GetValue(5));
            chat.Sort = dr.IsDBNull(6) ? 0 : dr.GetInt32(6);
            chat.IsEnable = dr.IsDBNull(7) ? false : dr.GetBoolean(7);
            chat.ProvinceName = dr.IsDBNull(8) ? "全部" : dr.GetString(8);
            chat.CityName = dr.IsDBNull(9) ? "全部" : dr.GetString(9);

            return chat;
        }

        internal static int GetChatCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectChat = @"SELECT COUNT([Chat].[ChatID]) FROM [Chat] 
                            LEFT OUTER JOIN Area AS AreaProvince ON [Chat].ProvinceID = AreaProvince.AreaID 
                            LEFT OUTER JOIN Area AS AreaCity ON [Chat].CityID = AreaCity.AreaID ";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectChat, ref parameterlst);
            DbCommand SelectChatCommand = db.GetSqlStringCommand(SelectChat);
            DbHelper.PackDbCommandByDbParameters(SelectChatCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectChatCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<ChatEntity> GetChatList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<ChatEntity> chatlst = new List<ChatEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectChat = @"
                    SELECT [ChatID],[Number],[Name],[ProvinceID],[CityID],[Created],[Sort],[IsEnable],[ProvinceName],[CityName]
                    FROM (
                        SELECT [Chat].[ChatID],[Chat].[Number],[Chat].[Name],[Chat].[ProvinceID],[Chat].[CityID],
                            [Chat].[Created],[Chat].[Sort],[Chat].[IsEnable],[AreaProvince].[Name] AS ProvinceName,
                            [AreaCity].[Name] AS CityName, ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectChat += " " + sortField + " " + sortMode + ",";
            SelectChat += @" [Chat].[ChatID] Desc
                    ) AS rowNum 
                        FROM [Chat] 
                            LEFT OUTER JOIN Area AS AreaProvince ON [Chat].ProvinceID = AreaProvince.AreaID 
                            LEFT OUTER JOIN Area AS AreaCity ON [Chat].CityID = AreaCity.AreaID ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectChat, ref parameterlst);
            SelectChat += @"
                    ) AS [Chat] 
                    WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectChatCommand = db.GetSqlStringCommand(SelectChat);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectChatCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectChatCommand))
            {
                while (dr.Read())
                {
                    chatlst.Add(GetChatByReader(dr));
                }
            }

            return chatlst;
        }

        internal static List<ChatEntity> GetChatList(IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<ChatEntity> chatlst = new List<ChatEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectChat = @"
                    SELECT [Chat].[ChatID],[Chat].[Number],[Chat].[Name],[Chat].[ProvinceID],[Chat].[CityID],
                            [Chat].[Created],[Chat].[Sort],[Chat].[IsEnable],[AreaProvince].[Name] AS ProvinceName,
                            [AreaCity].[Name] AS CityName
                    FROM [Chat] 
                            LEFT OUTER JOIN Area AS AreaProvince ON [Chat].ProvinceID = AreaProvince.AreaID 
                            LEFT OUTER JOIN Area AS AreaCity ON [Chat].CityID = AreaCity.AreaID ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectChat, ref parameterlst);
            SelectChat += " ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectChat += " " + sortField + " " + sortMode + ",";
            SelectChat += " [Chat].[ChatID] Desc ";

            DbCommand SelectChatCommand = db.GetSqlStringCommand(SelectChat);
            DbHelper.PackDbCommandByDbParameters(SelectChatCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectChatCommand))
            {
                while (dr.Read())
                {
                    chatlst.Add(GetChatByReader(dr));
                }
            }

            return chatlst;
        }

        internal static ChatEntity GetChat(int chatID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            ChatEntity chat = null;

            string SelectChat = @"
                    SELECT [Chat].[ChatID],[Chat].[Number],[Chat].[Name],[Chat].[ProvinceID],[Chat].[CityID],
                            [Chat].[Created],[Chat].[Sort],[Chat].[IsEnable],[AreaProvince].[Name] AS ProvinceName,
                            [AreaCity].[Name] AS CityName
                    FROM [Chat] LEFT OUTER JOIN Area AS AreaProvince ON [Chat].ProvinceID = AreaProvince.AreaID 
                            LEFT OUTER JOIN Area AS AreaCity ON [Chat].CityID = AreaCity.AreaID 
                    WHERE [Chat].[ChatID]=@ChatID
                ";

            DbCommand SelectChatCommand = db.GetSqlStringCommand(SelectChat);
            db.AddInParameter(SelectChatCommand, "@ChatID", DbType.Int32, chatID);

            using (IDataReader dr = db.ExecuteReader(SelectChatCommand))
            {
                if (dr.Read())
                {
                    chat = GetChatByReader(dr);
                }
            }

            return chat;
        }

        internal static int InsertChat(ChatEntity chat)
        {
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder InsertChat = new StringBuilder();
            List<DbParameter> parameterlst = new List<DbParameter>();

            InsertChat.Append(@"Declare @ChatID int;
                    INSERT INTO [Chat] 
				([Number],[Name],[ProvinceID],[CityID],[Created],[Sort],[IsEnable])
				 VALUES (@Number,@Name,@ProvinceID,@CityID,@Created,@Sort,@IsEnable);
                    SET @ChatID=@@IDENTITY;
                ");

            parameterlst.Add(DbHelper.PackInParameter(db, "@Number", DbType.String, chat.Number));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Name", DbType.String, chat.Name));
            parameterlst.Add(DbHelper.PackInParameter(db, "@ProvinceID", DbType.Int32, chat.ProvinceID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@CityID", DbType.Int32, chat.CityID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Created", DbType.DateTime, chat.Created));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Sort", DbType.Int32, chat.Sort));
            parameterlst.Add(DbHelper.PackInParameter(db, "@IsEnable", DbType.Boolean, chat.IsEnable));


            InsertChat.Append("SELECT @ChatID;");

            DbCommand InsertChatCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_InsertChat", InsertChat.ToString()));
            DbHelper.PackDbCommandByDbParameters(InsertChatCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(InsertChatCommand);

            return obj == null ? 0 : (int)obj;
        }

        internal static int UpdateChat(ChatEntity chat)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder UpdateChat = new StringBuilder();
            List<DbParameter> parameterlst = new List<DbParameter>();

            UpdateChat.Append(@"UPDATE [Chat] 
				 SET [Number]=@Number,[Name]=@Name,[ProvinceID]=@ProvinceID,[CityID]=@CityID,[Created]=@Created,[Sort]=@Sort,[IsEnable]=@IsEnable 
				 WHERE [ChatID] = @ChatID;");

            parameterlst.Add(DbHelper.PackInParameter(db, "@ChatID", DbType.Int32, chat.ChatID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Number", DbType.String, chat.Number));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Name", DbType.String, chat.Name));
            parameterlst.Add(DbHelper.PackInParameter(db, "@ProvinceID", DbType.Int32, chat.ProvinceID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@CityID", DbType.Int32, chat.CityID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Created", DbType.DateTime, chat.Created));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Sort", DbType.Int32, chat.Sort));
            parameterlst.Add(DbHelper.PackInParameter(db, "@IsEnable", DbType.Boolean, chat.IsEnable));


            DbCommand UpdateChatCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_UpdateChat", UpdateChat.ToString()));
            DbHelper.PackDbCommandByDbParameters(UpdateChatCommand, parameterlst.ToArray());

            result = db.ExecuteNonQuery(UpdateChatCommand);

            return result;
        }

        internal static int DeleteChat(int chatID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteChat = @"
                DELETE FROM [Chat] WHERE ChatID=@ChatID";

            DbCommand DeleteChatCommand = db.GetSqlStringCommand(DeleteChat);
            db.AddInParameter(DeleteChatCommand, "@ChatID", DbType.Int32, chatID);
            result = db.ExecuteNonQuery(DeleteChatCommand);

            return result;
        }

        internal static int SetChatEnable(int chatID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetChat = @"UPDATE [Chat]
                    SET [IsEnable] = ([IsEnable]+1)%2
                    WHERE [ChatID] = @ChatID";

            DbCommand SetChatCommand = db.GetSqlStringCommand(SetChat);
            db.AddInParameter(SetChatCommand, "@ChatID", DbType.Int32, chatID);
            result = db.ExecuteNonQuery(SetChatCommand);

            return result;
        }

        internal static int HitChat(int chatID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetChat = @"UPDATE [Chat]
                    SET [Hits] = [Hits]+1
                    WHERE [ChatID] = @ChatID";

            DbCommand SetChatCommand = db.GetSqlStringCommand(SetChat);
            db.AddInParameter(SetChatCommand, "@ChatID", DbType.Int32, chatID);
            result = db.ExecuteNonQuery(SetChatCommand);

            return result;
        }
    }
}
